﻿using Common_ClassLibrary;
using CreateTable_OptionDataBase_ConsoleApp.Models;
using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CreateTable_OptionDataBase_ConsoleApp
{
    class Program
    {
        static string destDataTableName = "Tab_Student";

        static string sourceTableName = "Tab_Source";
        static void Main(string[] args)
        {
            string dataPath = "test.db";
            if (!File.Exists(dataPath))
            {
                using (File.Create(dataPath))
                {

                }
            }

            string conStr = $"datasource={dataPath}";
            SqliteConnection connection = new SqliteConnection(conStr);
            connection.Open();

            if (!IsExistTableByName(connection, destDataTableName))
            {
                CreateStudentTable(connection, destDataTableName);
            }

            if (!IsExistTableByName(connection, sourceTableName))
            {
                CreateSourceTable(connection, sourceTableName);
            }

            Student student = new Student()
            {
                Name = "王小二" + DateTime.Now.Second,
                Age = 10,
                Address = "天国",
                Telephone = "123456",
            };
            bool insertFlag = InsertOneSutdentInfo(connection, student, destDataTableName);
            Console.WriteLine($"插入学生信息{insertFlag}");



            Student studentUpdate = new Student()
            {
                ID = 1,
                Name = "王小二" + DateTime.Now.Second,
                Age = 10,
                Address = "天国_UpDate",
                Telephone = "123456_UpDate",
            };
            bool updateFlag = UpDateStudentInfo(connection, studentUpdate);
            Console.WriteLine($"更新数据{updateFlag}");

            int deletID = 2;
            bool deleteFlag = DeleteStudentByID(connection, deletID);
            Console.WriteLine($"删除学生ID={deletID}信息{deleteFlag}");

            List<Student> allStudnetList = SelectAllStudnetsInfo(connection);

            Console.WriteLine("---------数据库中所有学生信息--------------");
            allStudnetList.ForEach(s => { Console.WriteLine(s.FormaterShowInfo()); });
            Console.WriteLine("---------end--------------");

            connection.Close();
            Console.ReadKey();
            #region 测试通用帮助类
            Console.WriteLine("********************************************************************************");
            Console.WriteLine($"测试帮助类。。。");
            Sqlite64Helper dataOptionHelper = new Sqlite64Helper(conStr);

            Console.WriteLine("---------数据库中所有学生信息--------------");
            dataOptionHelper.ExecuteReader<Student>($"select * from {destDataTableName} where ID>@id", new Dictionary<string, object> { { "@id", 2 } }).ForEach(s => { Console.WriteLine(s.FormaterShowInfo()); });
            Console.WriteLine("---------end--------------");

            #region 联合查询
            var sourceLs = dataOptionHelper.ExecuteReader<SourceInfo>($"select * from {sourceTableName}", null);

            //最好用每个表中字段代替 *
            string unionSelectSqlStr = $"select * from {destDataTableName} inner join {sourceTableName} on {destDataTableName}.ID=={sourceTableName}.blogStudentID";
            var reader = dataOptionHelper.ExecuteReader(unionSelectSqlStr, null);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader[0]}\t{reader[1]}\t{reader[2]}\t{reader[3]}\t{reader[4]}\t{reader[5]}");
                }
            }
            reader.Close();
           
            #endregion

            Console.ReadKey();
            #endregion

        }

        private static void CreateSourceTable(SqliteConnection connection, string sourceTableName)
        {
            string sqlText = $"create table {sourceTableName} ( ID  INTEGER  Primary key  AUTOINCREMENT not null, subject nvarchar(16) not null , source float , blogStudentID INTEGER not null )";
            SqliteCommand com = new SqliteCommand(sqlText, connection);
            com.CommandType = System.Data.CommandType.Text;
            if (connection.State != System.Data.ConnectionState.Open)
            {
                connection.Open();
            }
            com.ExecuteNonQuery();

            //插入十条数据
            List<string> subjectLs = new List<string>() { "英语", "数学", "地理", "高数", "品德" };
            Random random = new Random();
            for (int i = 0; i < 100; i++)
            {
                com.CommandText = $"insert into {sourceTableName} (subject,source,blogStudentID) values ('{subjectLs[random.Next(100) % subjectLs.Count]}',{random.Next(100)},{random.Next(10)})";
                Console.WriteLine($"{sourceTableName} 表插入数据 {com.ExecuteNonQuery() > 0}");
            }

            com.Dispose();
        }

        private static List<Student> SelectAllStudnetsInfo(SqliteConnection connection)
        {
            List<Student> ls = new List<Student>();
            using (SqliteCommand com = new SqliteCommand())
            {
                com.Connection = connection;
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    connection.Open();
                }
                com.CommandText = $"select ID,name,age,telephone,address  from {destDataTableName}";
                SqliteDataReader dataReader = com.ExecuteReader();
                if (dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        Student s = new Student();
                        s.ID = (int)dataReader.GetInt64(0);
                        s.Name = dataReader.GetString(1);
                        s.Age = dataReader.GetInt32(2);
                        s.Telephone = dataReader.GetString(3);
                        s.Address = dataReader.GetString(4);
                        ls.Add(s);
                    }
                }
                //如果有多个结果集 可以使用
                if (dataReader.NextResult())
                {
                    //此时datareader 切换到下张结果表中
                }
                dataReader.Close();
            }
            return ls;
        }

        private static bool DeleteStudentByID(SqliteConnection connection, int v)
        {
            bool deletedFalg = false;
            using (SqliteCommand com = new SqliteCommand())
            {
                com.Connection = connection;
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    connection.Open();
                }
                com.CommandText = $"delete from {destDataTableName} where ID={v}";
                deletedFalg = com.ExecuteNonQuery() > 0;
            }

            return deletedFalg;
        }

        private static bool UpDateStudentInfo(SqliteConnection connection, Student studentUpdate)
        {
            bool updateFlag = false;
            if (connection.State != System.Data.ConnectionState.Open)
            {
                connection.Open();
            }
            //先判断，跟新的学生信息在数据库中是否存在
            using (SqliteCommand com = new SqliteCommand())
            {
                com.Connection = connection;
                com.CommandType = System.Data.CommandType.Text;
                com.CommandText = $"select count(1) from  {destDataTableName} where ID={studentUpdate.ID}";
                if ((long)com.ExecuteScalar() < 0)
                {
                    return updateFlag;
                }

                com.CommandText = $"update {destDataTableName} set name='{studentUpdate.Name}',age={studentUpdate.Age},telephone='{studentUpdate.Telephone}',address='{studentUpdate.Address}' where ID={studentUpdate.ID}";
                updateFlag = com.ExecuteNonQuery() > 0;
            }

            return updateFlag;
        }

        private static bool InsertOneSutdentInfo(SqliteConnection connection, Student student, string tableName)
        {
            bool insertFalg = false;
            using (SqliteCommand com = new SqliteCommand())
            {
                com.Connection = connection;
                com.CommandType = System.Data.CommandType.Text;
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    connection.Open();
                }
                //根据学生名判断学生信息是否存在
                com.CommandText = $"select count(1) from {tableName} where name = '{student.Name}'";
                if ((long)com.ExecuteScalar() > 0)
                {
                    return insertFalg;
                }

                com.CommandText = $"insert into {tableName} (name,age,telephone,address) values ('{student.Name}',{student.Age},'{student.Telephone}','{student.Address}')";

                insertFalg = com.ExecuteNonQuery() > 0;
            }

            return insertFalg;
        }

        /// <summary>
        /// 创建一个学生表
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="destDataTableName"></param>
        private static void CreateStudentTable(SqliteConnection connection, string destDataTableName)
        {
            string sqlText = $"create table {destDataTableName} ( ID  INTEGER  Primary key  AUTOINCREMENT, name Text not null , age int ,telephone varchar(32), address text )";
            SqliteCommand com = new SqliteCommand(sqlText, connection);
            com.CommandType = System.Data.CommandType.Text;
            if (connection.State != System.Data.ConnectionState.Open)
            {
                connection.Open();
            }
            com.ExecuteNonQuery();
            com.Dispose();
        }

        /// <summary>
        /// 判断在数据库中表是否存在
        /// </summary>
        /// <param name="con"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        static bool IsExistTableByName(SqliteConnection con, string tableName)
        {
            bool existFlag = false;
            if (con == null)
            {
                return existFlag;
            }
            if (con.State != System.Data.ConnectionState.Open)
            {
                con.Open();
            }
            string sqlText = $"select count(1) from sqlite_master where type='table' and name='{tableName}'";
            SqliteCommand com = new SqliteCommand(sqlText, con);
            var entityObj = com.ExecuteScalar();
            long rowNu = (long)entityObj;
            existFlag = rowNu > 0;
            com.Dispose();
            return existFlag;
        }
    }
}
